"""
Case Type   : show variables
Case Name   : where子句 in/not in
Create At   : 2022/09/20
Owner       : zou_jialiang050
Description :
    1、create database
    2、执行show variables+in
    3、执行show variables+not in
    4、执行show global variables+in
    5、执行show session variables+in
    6、执行show variables+in/not in
    7、执行show global variables+in/not in
    8、执行show session variables+in/not in
    9、清理环境
Expect      :
    1、创建成功
    2、执行成功
    3、执行成功
    4、执行成功
    5、执行成功
    6、执行成功
    7、执行成功
    8、执行成功
    9、清理环境成功
History     :
    modified by @zou_jialiang050,2023.01.18，参数个数变更，修改用例
    modified by @zou_jialiang050,2023.01.28，调整筛选条件规避因shared_buffers参数值
    变更引起的show variables展示数量的错误
    modified by @zou_jialiang050,2023.02.03，调整筛选条件避免将端口号列入
    show variables的展示结果中
    modified by @zou_jialiang050,2023.03.09，调整筛选条件规避结果数量变更问题
    modified by @zou_jialiang050,2023.06.05，调整筛选条件规避结果数量变更问题
    modified by @zou_jialiang050 at 2024/06/21 根据最新代码优化用例
"""

import unittest
import os

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class VariablesCase(unittest.TestCase):

    def setUp(self):
        self.logger = Logger()
        self.logger.info(f"-----{os.path.basename(__file__)} start-----")
        self.commonsh = CommonSH("PrimaryDbUser")
        self.common = Common()
        self.constant = Constant()
        self.db_name = "db_var_0013"

    def test_variables(self):
        part1_text = "-----part1:variables_name-----"
        self.logger.info(part1_text)

        step1_text = "-----step1:create database;expect:创建成功-----"
        self.logger.info(step1_text)
        sql_cmd1 = f"drop database if exists {self.db_name};" \
                   f"create database {self.db_name} dbcompatibility = 'B';"
        self.logger.info(sql_cmd1)
        sql_res1 = self.commonsh.execut_db_sql(sql_cmd1)
        self.logger.info(sql_res1)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res1,
                      "创建数据库失败:" + step1_text)

        step2_text = "-----step2:show variables+in;expect:查询成功-----"
        self.logger.info(step2_text)
        sql_cmd2 = f"show variables where variable_name in " \
                   f"('exit_on_error','explain_perf_mode'," \
                   f"'enable_partitionwise');"
        self.logger.info(sql_cmd2)
        sql_res2 = self.commonsh.execut_db_sql(sql_cmd2,
                                               dbname=f'{self.db_name}')
        self.logger.info(sql_res2)
        sql_res2_list = ['exit_on_error', 'explain_perf_mode',
                         'enable_partitionwise']
        for variables_name in sql_res2_list:
            self.assertIn(variables_name, sql_res2, "查询失败：" + step2_text)

        step3_text = "-----step3:show variables+not in;expect:查询成功-----"
        self.logger.info(step3_text)
        sql_cmd3 = f"show variables where variable_name not in " \
                   f"('effective_cache_size','explain_perf_mode'," \
                   f"'enable_partitionwise') and variable_name like(" \
                   f"'e%_set_variable_b_%t');"
        self.logger.info(sql_cmd3)
        sql_res3 = self.commonsh.execut_db_sql(sql_cmd3,
                                               dbname=f'{self.db_name}')
        self.logger.info(sql_res3)
        self.assertIn("(1 row)", sql_res3, "查询失败：" + step3_text)

        step4_text = "-----step4:show global variables+in;expect:查询成功-----"
        self.logger.info(step4_text)
        sql_cmd4 = f"show global variables where variable_name in " \
                   f"('wal_level','temp_buffers'," \
                   f"'log_statement','data_sync_retry');"
        self.logger.info(sql_cmd4)
        sql_res4 = self.commonsh.execut_db_sql(sql_cmd4,
                                               dbname=f'{self.db_name}')
        self.logger.info(sql_res4)
        sql_res4_list = ['wal_level', 'temp_buffers', 'log_statement',
                         'data_sync_retry']
        for variables_name in sql_res4_list:
            self.assertIn(variables_name,
                          sql_res4, "查询失败：" + step4_text)

        step5_text = "-----step5:show session variables+in;" \
                     "expect:查询成功-----"
        self.logger.info(step5_text)
        sql_cmd5 = f"show session variables where variable_name in " \
                   f"('work_mem','upgrade_mode');"
        self.logger.info(sql_cmd5)
        sql_res5 = self.commonsh.execut_db_sql(sql_cmd5,
                                               dbname=f'{self.db_name}')
        self.logger.info(sql_res5)
        sql_res5_list = ['work_mem', 'upgrade_mode']
        for variables_name in sql_res5_list:
            self.assertIn(variables_name, sql_res5, "查询失败：" + step5_text)

        part2_text = "-----part2:values-----"
        self.logger.info(part2_text)

        step6_text = "-----step6:show variables+in/not in;" \
                     "expect:查询成功-----"
        self.logger.info(step6_text)
        sql_cmd6 = f"show variables where value in ('1024','500MB');"
        self.logger.info(sql_cmd6)
        sql_res6 = self.commonsh.execut_db_sql(sql_cmd6,
                                               dbname=f'{self.db_name}')
        self.logger.info(sql_res6)
        self.assertIn("(7 rows)", sql_res6, "查询失败：" + step6_text)
        sql_cmd7 = f"show variables where value not in ('256','on','off') " \
                   f"and value like ('40%6');"
        self.logger.info(sql_cmd7)
        sql_res7 = self.commonsh.execut_db_sql(sql_cmd7,
                                               dbname=f'{self.db_name}')
        self.logger.info(sql_res7)
        self.assertIn("(2 rows)", sql_res7, "查询失败：" + step6_text)

        step7_text = "-----step7:show global variables+in/not in;" \
                     "expect:查询成功-----"
        self.logger.info(step7_text)
        sql_cmd8 = f"show global variables where value in ('1024','500MB');"
        self.logger.info(sql_cmd8)
        sql_res8 = self.commonsh.execut_db_sql(sql_cmd8,
                                               dbname=f'{self.db_name}')
        self.logger.info(sql_res8)
        self.assertIn("(4 rows)", sql_res8, "查询失败：" + step7_text)
        sql_cmd9 = f"show global variables " \
                   f"where value not in ('256','on','off') " \
                   f"and value like ('5_');"
        self.logger.info(sql_cmd9)
        sql_res9 = self.commonsh.execut_db_sql(sql_cmd9,
                                               dbname=f'{self.db_name}')
        self.logger.info(sql_res9)
        self.assertIn("(2 rows)", sql_res9, "查询失败：" + step7_text)

        step8_text = "-----step8:show session variables+in/not in;" \
                     "expect:查询成功-----"
        self.logger.info(step8_text)
        sql_cmd10 = f"show session variables where value in ('1024','500MB');"
        self.logger.info(sql_cmd10)
        sql_res10 = self.commonsh.execut_db_sql(sql_cmd10,
                                                dbname=f'{self.db_name}')
        self.logger.info(sql_res10)
        self.assertIn("(7 rows)", sql_res10, "查询失败：" + step8_text)
        sql_cmd11 = f"show session variables " \
                    f"where value not in ('256','on','off') " \
                    f"and value like ('50');"
        self.logger.info(sql_cmd11)
        sql_res11 = self.commonsh.execut_db_sql(sql_cmd11,
                                                dbname=f'{self.db_name}')
        self.logger.info(sql_res11)
        self.assertIn("(3 rows)", sql_res11, "查询失败：" + step8_text)

    def tearDown(self):
        step9_text = "-----step9:清理环境;expect:成功-----"
        self.logger.info(step9_text)

        drop_db_text = "-----drop database-----"
        self.logger.info(drop_db_text)
        drop_db_cmd = f'''drop database if exists {self.db_name};'''
        self.logger.info(drop_db_cmd)
        drop_db_res = self.commonsh.execut_db_sql(drop_db_cmd)
        self.logger.info(drop_db_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS,
                      drop_db_res, "drop database filed" + drop_db_res)

        self.logger.info(f"-----{os.path.basename(__file__)} end-----")
